package com.lb.jwitter.hibernate;

import org.apache.commons.logging.Log;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;
import com.lb.utils.orm.hibernate.HibernateGenericDao;
import org.springframework.transaction.annotation.Transactional;

import com.lb.jwitter.Tbl_CTDT_ChuyenNganh_MonHoc;
import com.lb.jwitter.Tbl_DKMH_DotDK_HocVien;
import com.lb.jwitter.Tbl_ThongTin_HocVien_MonHoc;

@Repository("tbl_DKMH_DotDK_HocVienDAO")
public class Tbl_DKMH_DotDK_HocVienHibernateDAO extends HibernateGenericDao<Tbl_DKMH_DotDK_HocVien, Long> implements Tbl_DKMH_DotDK_HocVienDAO {
   private Log log = LogFactory.getLog(Tbl_DKMH_DotDK_HocVienHibernateDAO.class);

   @Autowired
   public Tbl_DKMH_DotDK_HocVienHibernateDAO(@Qualifier("sessionFactory")   SessionFactory sessionFactory) {
       super(sessionFactory);
      
   }

   @SuppressWarnings("unchecked")
@Transactional
   public final List<Tbl_DKMH_DotDK_HocVien> findAll(int pageSize, int page) {
       if( pageSize == 0) return getSession().createCriteria(Tbl_DKMH_DotDK_HocVien.class).list(); 
       return getSession().createCriteria(Tbl_DKMH_DotDK_HocVien.class).setFirstResult(pageSize * (page-1)).setMaxResults(pageSize).list();
   }
   
@Transactional
   public final Long save(Tbl_DKMH_DotDK_HocVien _newInstance) {
   	return super.save(_newInstance);
   }

@Transactional
   public final boolean exists(Long id) {
       boolean result = (Integer) getSession().createCriteria(Tbl_DKMH_DotDK_HocVien.class).setProjection(Projections.rowCount()).add(
                       Restrictions.eq("id", id)).uniqueResult() == 1;
       log.info("Result: "+result);
   	return result;
   }

@Transactional
   public Collection<Tbl_DKMH_DotDK_HocVien> find(Tbl_DKMH_DotDK_HocVien obj,int pageSize,int page){
   	Collection<Tbl_DKMH_DotDK_HocVien> _tbl_DKMH_DotDK_HocViens = new ArrayList<Tbl_DKMH_DotDK_HocVien>();
   	try {
   		String sql = "select d from tbl_DKMH_DotDK_HocVien as d where 1=1 ";
   sql += " and d.id = " + obj.getId() + " ";
   sql += " and d.dotDKId. = " + obj.getDotDKId().getId() + " ";
   sql += " and d.hocVienId. = " + obj.getHocVienId().getId() + " ";
   sql += " and d.soTCDangKyToiDa = " + obj.getSoTCDangKyToiDa() + " ";
   sql += " and d.KhongDangKyYN = " + obj.getKhongDangKyYN() + " ";
       if(obj.getKhongDangKyLyDo() != null && !obj.getKhongDangKyLyDo().equalsIgnoreCase("")){
   		sql +=  " and d.khongDangKyLyDo like '%" +obj.getKhongDangKyLyDo() + "%' " ;
   }
   sql += " and d.canBoId. = " + obj.getCanBoId().getId() + " ";
   if (obj.getNgayCapNhat() != null)
   {
       DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
       sql += " and d.ngayCapNhat = '" + df.format(obj.getNgayCapNhat()) + "' ";
   }
   		sql += " order by d.id DESC ";
   		_tbl_DKMH_DotDK_HocViens = getSession().createQuery(sql).setFirstResult(pageSize*(page-1)).setMaxResults(pageSize).list();
   	} catch (Exception e) {
   		e.printStackTrace();
   	}
   	return _tbl_DKMH_DotDK_HocViens;
   }
   

	@Transactional
	public Collection<Tbl_DKMH_DotDK_HocVien> filterBySoTinChiConNo(List<Tbl_DKMH_DotDK_HocVien> objs , int tuSoTinChiNo,int denSoTinChiNo){
	   	Collection<Tbl_DKMH_DotDK_HocVien> _tbl_DKMH_DotDK_HocViens = new ArrayList<Tbl_DKMH_DotDK_HocVien>();
	   	for(Tbl_DKMH_DotDK_HocVien obj : objs){
		   	try{
		   		  Criteria crit = getSession().createCriteria(Tbl_CTDT_ChuyenNganh_MonHoc.class, "cnmh");
		   		  crit.createCriteria("cnmh.monHocId","monHocId");
		   		  crit.createCriteria("cnmh.chuyenNganhId","chuyenNganhId");
		   		  crit.add(Restrictions.eq("chuyenNganhId.id", obj.getHocVienId().getChuyenNganhId().getId()));
		   		  
			   	  ProjectionList proList = Projections.projectionList();
			   	  proList.add(Projections.sum("monHocId.soTinChi"));
			   	  crit.setProjection(proList);
			   	  List sumResult = crit.list();
			   	  
			   	  crit = getSession().createCriteria(Tbl_ThongTin_HocVien_MonHoc.class, "hvmh");
		   		  crit.createCriteria("hvmh.monHocId","monHocId");
		   		  crit.createCriteria("hvmh.hocVienId","hocVienId");
		   		  crit.add(Restrictions.eq("hocVienId.id", obj.getHocVienId().getId()));
		   		  crit.add(Restrictions.eq("trangThai","PASS"));
		   		  

			   	  crit.setProjection(proList);
			   	  List sumResult2 = crit.list();
			   	  
			   	  if(sumResult != null && sumResult.size() > 0 && sumResult2 != null && sumResult2.size() > 0){
			   		  try{
				   	  int tongTC = Integer.parseInt(sumResult.get(0).toString());
				   	  int tongTCDat = Integer.parseInt(sumResult2.get(0).toString());
				   	  
				   	  int soTCNo = tongTC - tongTCDat;
				   	  
				   	  boolean exist = false;
				   	  if(tuSoTinChiNo > 0){
				   		  //sotin chi no nam trong khoang [tuSoTCNo; denSoTCNo]
				   		  if(denSoTinChiNo > 0){
				   			  if(soTCNo >= tuSoTinChiNo && soTCNo <= denSoTinChiNo )
				   				  exist = true;
				   		  }
				   		  //
				   		  //sotin chi no nam trong khoang [tuSoTCNo; +8]
				   		  else{
				   			  if(soTCNo >= tuSoTinChiNo)
				   				  exist = true;
				   		  }
				   	  }else{
	
				   		  //sotin chi no nam trong khoang [-8; denSoTCNo]
				   		  if(denSoTinChiNo > 0){
				   			  if(soTCNo <= denSoTinChiNo )
				   				  exist = true;
				   		  }else{
				   				  exist = true;
				   		  }
				   	  }
				   	  
				   	  if(exist){
					   		_tbl_DKMH_DotDK_HocViens.add(obj);

				   	  }
			   		  }catch (Exception e) {
					   		e.printStackTrace();
			   		  }
			   	  }
		   	  
				
			} catch (Exception e) {
			   		e.printStackTrace();
			}
	   	}
	   	
		return _tbl_DKMH_DotDK_HocViens;
	}

}//endclass
